package com.smart4j.chapter.modules.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.smart4j.chapter.common.pool.PoolUtils;
import com.smart4j.chapter.modules.dao.ClientDao;
import com.smart4j.chapter.modules.entity.Client;

/**
 * description: new java files...TODO
 * @version v1.0
 * @author w
 * @date 2018年8月5日下午2:19:08
 *
 */

public class ClientDaoImpl implements ClientDao {

	@Override
	public Client get(String id) {
		Client client = new Client();
		PreparedStatement stat  = null ;
		ResultSet resultSet = null ;
		Connection connection = PoolUtils.getInstance().getConnection();
		String sql ="select * from client where id=?";
		try {
			stat = connection.prepareStatement(sql);
			stat.setString(1, id);
			resultSet = stat.executeQuery();
			while(resultSet.next()){
				this.extratValue(client, resultSet);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.closeSources(stat, resultSet, connection);
		}
		return client;
	}

	@Override
	public Client get(Client t) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int del(String id) {
		int result = 0 ;
		// 获取连接，设置不自动提交事务
		Connection connection = PoolUtils.getInstance().getConnection(false);
		PreparedStatement stat = null ;
		String sql ="UPDATE client set del_flag='"+Client.DEL_FLAG_DELETE+"' where id =?";
		try {
			stat = connection.prepareStatement(sql);
			stat.setString(1, id);
			result = stat.executeUpdate();
			// 提交事务
			connection.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			// 出现异常，回滚事务
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}finally{
			closeSources(stat, null, connection);
		}
		return result;
	}

	@Override
	public int del(Client t) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int update(Client client) {
		int result= 0;
		String sql =" update client set name = ? , contact = ?  , email = ? ,"
				+ "remarks = ? ,create_date = ? , update_date = ? , phone = ? where id = ?";
		Connection connection = PoolUtils.getInstance().getConnection();
		PreparedStatement stat = null;
		try {
			stat = connection.prepareStatement(sql);
			stat.setString(1, client.getName());
			stat.setString(2, client.getContact());
			stat.setString(3, client.getEmail());
			stat.setString(4, client.getRemarks());
			stat.setDate(5, new java.sql.Date(client.getCreateDate().getTime()));
		// 注： 使用setDate方法，得到的时间日期是 yyyy-MM-dd 的形式 如：  2018-08-10 00:00:00 
		//	stat.setDate(5, new java.sql.Date(client.getUpdateDate().getTime()));
			stat.setTimestamp(6, new Timestamp(client.getUpdateDate().getTime()));
			stat.setString(7,client.getPhone());
			stat.setString(8,client.getId());
			result = stat.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeSources(stat, null, connection);
		}
		return result;
	}

	@Override
	public int add(Client t) {
		int result= 0;
		Connection connection = PoolUtils.getInstance().getConnection(false);
		PreparedStatement stat = null ;
		String sql = "INSERT into client (name,contact,phone,email,remarks,del_flag "
					+" ,create_date,create_by,update_date,update_by) "
					+" VALUES (?,?,?,?,?,?,?,?,?,?)";
		try {
			stat = connection.prepareStatement(sql);
			stat.setString(1, t.getName());
			stat.setString(2, t.getContact());
			stat.setString(3, t.getPhone());
			stat.setString(4, t.getEmail());
			stat.setString(5, t.getRemarks());
			stat.setString(6, t.getDelFlag());
			stat.setTimestamp(7, new Timestamp(t.getCreateDate().getTime()));
			stat.setString(8, t.getCreateBy());
			stat.setTimestamp(9, new Timestamp(t.getUpdateDate()== null?new Date().getTime():t.getUpdateDate().getTime()));
			stat.setString(10, t.getUpdateBy());
			result = stat.executeUpdate();
			connection.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				// 产生异常，回滚事务。
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}finally{
			closeSources(stat, null, connection);
		}
		return result;
	}

	@Override
	public List<Client> findList(Client t) {
		List<Client> list = new ArrayList<>();
		String sql ="select * from client where del_flag = ? order by update_date desc" ;
		PreparedStatement stat  = null ;
		ResultSet resultSet = null ;
		Connection connection = PoolUtils.getInstance().getConnection();
		try {
			stat = connection.prepareStatement(sql);
			stat.setString(1, t.getDelFlag());
			resultSet = stat.executeQuery();
			while(resultSet.next()){
				t = new Client();
				extratValue(t,resultSet);
				list.add(t);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			closeSources(stat, resultSet, connection);
		}
		return list;
	}
	
	/**
	 * 关闭和释放资源
	 * @param stat
	 * @param resultSet
	 * @param connection
	 */
	private void closeSources(PreparedStatement stat, ResultSet resultSet, Connection connection) {
		if(null != stat){
			try {
				stat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(null != resultSet){
			try {
				resultSet.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		PoolUtils.getInstance().release(connection);
	}

	/**
	 * description: 从 ResultSet 中提取值 set 到 Client 对象中
	 * @param t
	 * @param resultSet
	 * void 
	 * @version v1.0
	 * @author w
	 * @date 2018年8月10日 上午11:11:00
	 */
	private void extratValue(Client t, ResultSet resultSet) {
		try {
			// 注意 index 从 1开始，不是0. 否则会抛出异常：Column Index out of range, 0 < 1.
			String id = resultSet.getString(1);
			String name = resultSet.getString(2);
			String contact = resultSet.getString(3);
			String phone = resultSet.getString(4);
			String email = resultSet.getString("email");
			String remarks = resultSet.getString("remarks");
			Date createDate = resultSet.getDate("create_date");
			String createBy = resultSet.getString("create_by");
			Date updateDate = resultSet.getDate("update_date");
			String updateBy = resultSet.getString("update_by");
			t.setId(id);
			t.setName(name);
			t.setContact(contact);
			t.setPhone(phone);
			t.setEmail(email);
			t.setRemarks(remarks);
			t.setCreateDate(createDate);
			t.setCreateBy(createBy);
			t.setUpdateDate(updateDate);
			t.setUpdateBy(updateBy);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	@Override
	public List<Client> findPage(Client t) {
		// TODO Auto-generated method stub
		return null;
	}

}
